﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace YJ.Data.MySql
{
    public class ShortMessage : YJ.Data.Interface.IShortMessage
    {
        private DBHelper dbHelper = new DBHelper();
        /// <summary>
        /// 构造函数
        /// </summary>
        public ShortMessage()
        {
        }
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="model">YJ.Data.Model.ShortMessage实体类</param>
        /// <returns>操作所影响的行数</returns>
        public int Add(YJ.Data.Model.ShortMessage model)
        {
            string sql = @"INSERT INTO shortmessage
				(ID,Title,Contents,SendUserID,SendUserName,ReceiveUserID,ReceiveUserName,SendTime,LinkUrl,LinkID,Type,Files) 
				VALUES(@ID,@Title,@Contents,@SendUserID,@SendUserName,@ReceiveUserID,@ReceiveUserName,@SendTime,@LinkUrl,@LinkID,@Type,@Files)";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@ID", MySqlDbType.VarChar, 36){ Value = model.ID },
				new MySqlParameter("@Title", MySqlDbType.Text, -1){ Value = model.Title },
				model.Contents == null ? new MySqlParameter("@Contents", MySqlDbType.VarChar, 4000) { Value = DBNull.Value } : new MySqlParameter("@Contents", MySqlDbType.VarChar, 4000) { Value = model.Contents },
				new MySqlParameter("@SendUserID", MySqlDbType.VarChar, 36){ Value = model.SendUserID },
				new MySqlParameter("@SendUserName", MySqlDbType.Text, -1){ Value = model.SendUserName },
				new MySqlParameter("@ReceiveUserID", MySqlDbType.VarChar, 36){ Value = model.ReceiveUserID },
				new MySqlParameter("@ReceiveUserName", MySqlDbType.Text, -1){ Value = model.ReceiveUserName },
				new MySqlParameter("@SendTime", MySqlDbType.DateTime, -1){ Value = model.SendTime },
				model.LinkUrl == null ? new MySqlParameter("@LinkUrl", MySqlDbType.VarChar, 2000) { Value = DBNull.Value } : new MySqlParameter("@LinkUrl", MySqlDbType.VarChar, 2000) { Value = model.LinkUrl },
				model.LinkID == null ? new MySqlParameter("@LinkID", MySqlDbType.VarChar, 50) { Value = DBNull.Value } : new MySqlParameter("@LinkID", MySqlDbType.VarChar, 50) { Value = model.LinkID },
				new MySqlParameter("@Type", MySqlDbType.Int32, 11){ Value = model.Type },
				new MySqlParameter("@Files", MySqlDbType.Text, -1){ Value = model.Files }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="model">YJ.Data.Model.ShortMessage实体类</param>
        public int Update(YJ.Data.Model.ShortMessage model)
        {
            string sql = @"UPDATE shortmessage SET 
				Title=@Title,Contents=@Contents,SendUserID=@SendUserID,SendUserName=@SendUserName,ReceiveUserID=@ReceiveUserID,ReceiveUserName=@ReceiveUserName,SendTime=@SendTime,LinkUrl=@LinkUrl,LinkID=@LinkID,Type=@Type,Files=@Files
				WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@Title", MySqlDbType.Text, -1){ Value = model.Title },
				model.Contents == null ? new MySqlParameter("@Contents", MySqlDbType.VarChar, 4000) { Value = DBNull.Value } : new MySqlParameter("@Contents", MySqlDbType.VarChar, 4000) { Value = model.Contents },
				new MySqlParameter("@SendUserID", MySqlDbType.VarChar, 36){ Value = model.SendUserID },
				new MySqlParameter("@SendUserName", MySqlDbType.Text, -1){ Value = model.SendUserName },
				new MySqlParameter("@ReceiveUserID", MySqlDbType.VarChar, 36){ Value = model.ReceiveUserID },
				new MySqlParameter("@ReceiveUserName", MySqlDbType.Text, -1){ Value = model.ReceiveUserName },
				new MySqlParameter("@SendTime", MySqlDbType.DateTime, -1){ Value = model.SendTime },
				model.LinkUrl == null ? new MySqlParameter("@LinkUrl", MySqlDbType.VarChar, 2000) { Value = DBNull.Value } : new MySqlParameter("@LinkUrl", MySqlDbType.VarChar, 2000) { Value = model.LinkUrl },
				model.LinkID == null ? new MySqlParameter("@LinkID", MySqlDbType.VarChar, 50) { Value = DBNull.Value } : new MySqlParameter("@LinkID", MySqlDbType.VarChar, 50) { Value = model.LinkID },
				new MySqlParameter("@Type", MySqlDbType.Int32, 11){ Value = model.Type },
				new MySqlParameter("@Files", MySqlDbType.Text, -1){ Value = model.Files },
				new MySqlParameter("@ID", MySqlDbType.VarChar, 36){ Value = model.ID }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        public int Delete(Guid id)
        {
            string sql = "DELETE FROM shortmessage WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@ID", MySqlDbType.VarChar, 36){ Value = id.ToString() }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 将DataRedar转换为List
        /// </summary>
        private List<YJ.Data.Model.ShortMessage> DataReaderToList(MySqlDataReader dataReader)
        {
            List<YJ.Data.Model.ShortMessage> List = new List<YJ.Data.Model.ShortMessage>();
            YJ.Data.Model.ShortMessage model = null;
            while (dataReader.Read())
            {
                model = new YJ.Data.Model.ShortMessage();
                model.ID = dataReader.GetString(0).ToGuid();
                model.Title = dataReader.GetString(1);
                if (!dataReader.IsDBNull(2))
                    model.Contents = dataReader.GetString(2);
                model.SendUserID = dataReader.GetString(3).ToGuid();
                model.SendUserName = dataReader.GetString(4);
                model.ReceiveUserID = dataReader.GetString(5).ToGuid();
                model.ReceiveUserName = dataReader.GetString(6);
                model.SendTime = dataReader.GetDateTime(7);
                if (!dataReader.IsDBNull(8))
                    model.LinkUrl = dataReader.GetString(8);
                if (!dataReader.IsDBNull(9))
                    model.LinkID = dataReader.GetString(9);
                model.Type = dataReader.GetInt32(10);
                if (!dataReader.IsDBNull(11))
                    model.Files = dataReader.GetString(11);
                if (dataReader.FieldCount > 11)
                {
                    model.Status = dataReader.GetInt32(12);
                }
                else
                {
                    model.Status = 0;
                }
                List.Add(model);
            }
            return List;
        }
        /// <summary>
        /// 查询所有记录
        /// </summary>
        public List<YJ.Data.Model.ShortMessage> GetAll()
        {
            string sql = "SELECT *,0 AS Status FROM shortmessage";
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql);
            List<YJ.Data.Model.ShortMessage> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }
        /// <summary>
        /// 查询记录数
        /// </summary>
        public long GetCount()
        {
            string sql = "SELECT COUNT(*) FROM shortmessage";
            long count;
            return long.TryParse(dbHelper.GetFieldValue(sql), out count) ? count : 0;
        }
        /// <summary>
        /// 根据主键查询一条记录
        /// </summary>
        public YJ.Data.Model.ShortMessage Get(Guid id)
        {
            string sql = "SELECT *,0 AS Status FROM shortmessage WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@ID", MySqlDbType.VarChar, 36){ Value = id.ToString() }
			};
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<YJ.Data.Model.ShortMessage> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List.Count > 0 ? List[0] : null;
        }

        /// <summary>
        /// 根据主键查询一条已读记录
        /// </summary>
        public YJ.Data.Model.ShortMessage GetRead(Guid id)
        {
            string sql = "SELECT *,0 AS Status FROM ShortMessage1 WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@ID", MySqlDbType.VarChar){ Value = id.ToString() }
			};
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<YJ.Data.Model.ShortMessage> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List.Count > 0 ? List[0] : null;
        }

        /// <summary>
        /// 查询所有未读记录
        /// </summary>
        public List<YJ.Data.Model.ShortMessage> GetAllNoRead()
        {
            string sql = "SELECT *,0 AS Status FROM ShortMessage";
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql);
            List<YJ.Data.Model.ShortMessage> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }

        /// <summary>
        /// 查询一个人员未读记录
        /// </summary>
        public List<YJ.Data.Model.ShortMessage> GetAllNoReadByUserID(Guid userID)
        {
            string sql = "SELECT *,0 AS Status FROM ShortMessage WHERE ReceiveUserID=@ReceiveUserID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@ReceiveUserID", MySqlDbType.VarChar){ Value = userID.ToString() }
			};
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<YJ.Data.Model.ShortMessage> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }

        /// <summary>
        /// 将一个消息标记为已读
        /// </summary>
        public int UpdateStatus(Guid id)
        {
            string sql = "INSERT INTO ShortMessage1 SELECT * FROM ShortMessage WHERE ID=@ID;DELETE FROM ShortMessage WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@ID", MySqlDbType.VarChar){ Value = id.ToString() }
			};
            return dbHelper.Execute(sql, parameters);
        }


        public List<YJ.Data.Model.ShortMessage> GetList(out string pager, int[] status, string query = "", string title = "", string contents = "", string senderID = "", string date1="", string date2="", string receiveID="")
        {
            List<MySqlParameter> parList = new List<MySqlParameter>();
            string tempSql = string.Empty;
            if (status.Length == 1 && status[0] == 0)
            {
                tempSql = "SELECT *,0 AS Status FROM ShortMessage WHERE 1=1";
            }
            else if (status.Length == 1 && status[0] == 1)
            {
                tempSql = "SELECT *,1 AS Status FROM ShortMessage1 WHERE 1=1";
            }
            else if (status.Length == 2)
            {
                tempSql = "SELECT * FROM(SELECT *,0 AS Status FROM ShortMessage WHERE 1=1 UNION ALL SELECT *,1 AS Status FROM ShortMessage1 WHERE 1=1) temp WHERE 1=1";
            }
            StringBuilder sql = new StringBuilder(tempSql);
            if (receiveID.IsGuid())
            {
                sql.Append(" AND ReceiveUserID=@ReceiveUserID");
                parList.Add(new MySqlParameter("@ReceiveUserID", receiveID));
            }
            if (!title.IsNullOrEmpty())
            {
                sql.Append(" AND INSTR(Title,@Title)>0");
                parList.Add(new MySqlParameter("@Title", title));
            }
            
            if (!contents.IsNullOrEmpty())
            {
                sql.Append(" AND INSTR(Contents,@Contents)>0");
                parList.Add(new MySqlParameter("@Contents", contents));
            }

            if (!senderID.IsNullOrEmpty())
            {
                sql.AppendFormat(" AND SendUserID IN({0})",senderID);
                //parList.Add(new MySqlParameter("@SendUserID", MySqlDbType.VarChar, 8000) { Value = senderID });
            }

            if (date1.IsDateTime())
            {
                sql.Append(" AND SendTime>=@SendTime");
                parList.Add(new MySqlParameter("@SendTime", date1.ToDateTime()));
            }

            if (date2.IsDateTime())
            {
                sql.Append(" AND SendTime<=@SendTime1");
                parList.Add(new MySqlParameter("@SendTime1", date2.ToDateTime().AddDays(1).ToDateString()));
            }
            sql.Append(" ORDER BY SendTime DESC");

            long count;
            int size = YJ.Utility.Tools.GetPageSize();
            int number = YJ.Utility.Tools.GetPageNumber();
            string sql1 = dbHelper.GetPaerSql(sql.ToString(), size, number, out count, parList.ToArray());
            pager = YJ.Utility.Tools.GetPagerHtml(count, size, number, query);

            //System.Web.HttpContext.Current.Response.Write(sql1);
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql1, parList.ToArray());
            List<YJ.Data.Model.ShortMessage> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }

        public List<YJ.Data.Model.ShortMessage> GetList(out long count, int[] status, int size, int number, string title = "", string contents = "", string senderID = "", string date1 = "", string date2 = "", string receiveID = "", string order = "")
        {
            List<MySqlParameter> parList = new List<MySqlParameter>();
            string tempSql = string.Empty;
            if (status.Length == 1 && status[0] == 0)
            {
                tempSql = "SELECT *,0 AS Status FROM ShortMessage WHERE 1=1";
            }
            else if (status.Length == 1 && status[0] == 1)
            {
                tempSql = "SELECT *,1 AS Status FROM ShortMessage1 WHERE 1=1";
            }
            else if (status.Length == 2)
            {
                tempSql = "SELECT * FROM(SELECT *,0 AS Status FROM ShortMessage WHERE 1=1 UNION ALL SELECT *,1 AS Status FROM ShortMessage1 WHERE 1=1) temp WHERE 1=1";
            }
            StringBuilder sql = new StringBuilder(tempSql);
            if (receiveID.IsGuid())
            {
                sql.Append(" AND ReceiveUserID=@ReceiveUserID");
                parList.Add(new MySqlParameter("@ReceiveUserID", receiveID));
            }
            if (!title.IsNullOrEmpty())
            {
                sql.Append(" AND INSTR(Title,@Title)>0");
                parList.Add(new MySqlParameter("@Title", title));
            }

            if (!contents.IsNullOrEmpty())
            {
                sql.Append(" AND INSTR(Contents,@Contents)>0");
                parList.Add(new MySqlParameter("@Contents", contents));
            }

            if (!senderID.IsNullOrEmpty())
            {
                sql.AppendFormat(" AND SendUserID IN({0})", senderID);
                //parList.Add(new MySqlParameter("@SendUserID", MySqlDbType.VarChar, 8000) { Value = senderID });
            }

            if (date1.IsDateTime())
            {
                sql.Append(" AND SendTime>=@SendTime");
                parList.Add(new MySqlParameter("@SendTime", date1.ToDateTime()));
            }

            if (date2.IsDateTime())
            {
                sql.Append(" AND SendTime<=@SendTime1");
                parList.Add(new MySqlParameter("@SendTime1", date2.ToDateTime().AddDays(1).ToDateString()));
            }
            sql.Append(" ORDER BY " + (order.IsNullOrEmpty() ? "SendTime DESC" : order));

            string sql1 = dbHelper.GetPaerSql(sql.ToString(), size, number, out count, parList.ToArray());
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql1, parList.ToArray());
            List<YJ.Data.Model.ShortMessage> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }
        public List<YJ.Data.Model.ShortMessage> GetList( int[] status, string title = "", string contents = "", string senderID = "", string date1 = "", string date2 = "", string receiveID = "", string order = "")
        {
            List<MySqlParameter> parList = new List<MySqlParameter>();
            string tempSql = string.Empty;
            if (status.Length == 1 && status[0] == 0)
            {
                tempSql = "SELECT *,0 AS Status FROM ShortMessage WHERE 1=1";
            }
            else if (status.Length == 1 && status[0] == 1)
            {
                tempSql = "SELECT *,1 AS Status FROM ShortMessage1 WHERE 1=1";
            }
            else if (status.Length == 2)
            {
                tempSql = "SELECT * FROM(SELECT *,0 AS Status FROM ShortMessage WHERE 1=1 UNION ALL SELECT *,1 AS Status FROM ShortMessage1 WHERE 1=1) temp WHERE 1=1";
            }
            StringBuilder sql = new StringBuilder(tempSql);
            if (receiveID.IsGuid())
            {
                sql.Append(" AND ReceiveUserID=@ReceiveUserID");
                parList.Add(new MySqlParameter("@ReceiveUserID", receiveID));
            }
            if (!title.IsNullOrEmpty())
            {
                sql.Append(" AND INSTR(Title,@Title)>0");
                parList.Add(new MySqlParameter("@Title", title));
            }

            if (!contents.IsNullOrEmpty())
            {
                sql.Append(" AND INSTR(Contents,@Contents)>0");
                parList.Add(new MySqlParameter("@Contents", contents));
            }

            if (!senderID.IsNullOrEmpty())
            {
                sql.AppendFormat(" AND SendUserID IN({0})", senderID);
                //parList.Add(new MySqlParameter("@SendUserID", MySqlDbType.VarChar, 8000) { Value = senderID });
            }

            if (date1.IsDateTime())
            {
                sql.Append(" AND SendTime>=@SendTime");
                parList.Add(new MySqlParameter("@SendTime", date1.ToDateTime()));
            }

            if (date2.IsDateTime())
            {
                sql.Append(" AND SendTime<=@SendTime1");
                parList.Add(new MySqlParameter("@SendTime1", date2.ToDateTime().AddDays(1).ToDateString()));
            }
            sql.Append(" ORDER BY " + (order.IsNullOrEmpty() ? "SendTime DESC" : order));

            MySqlDataReader dataReader = dbHelper.GetDataReader(sql.ToString(), parList.ToArray());
            List<YJ.Data.Model.ShortMessage> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        public int Delete(string linkID, int Type)
        {
            string sql = "DELETE FROM ShortMessage WHERE LinkID=@LinkID AND Type=@Type";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@LinkID", MySqlDbType.VarChar){ Value = linkID },
                new MySqlParameter("@Type", MySqlDbType.Int32){ Value = Type }
			};
            return dbHelper.Execute(sql, parameters);
        }

    }
}